import datetime

import matplotlib.pyplot as plt
import pandas as pd
import pymysql
from dateutil.relativedelta import relativedelta
from fastapi import FastAPI
from sklearn.ensemble import BaggingRegressor, RandomForestClassifier
from sklearn.model_selection import train_test_split as TTS
from sklearn.neural_network import MLPClassifier as DNN
from sklearn.tree import DecisionTreeClassifier

app = FastAPI()
pointNames = "('SYJY59','SYJY61','SYJY65','SYJY60','SYJY70','SYJY32','SYJY27')"
pointNameDict = {"SYJY59": 1, "SYJY61": 2, "SYJY65": 3, "SYJY60": 4, "SYJY70": 5, "SYJY32": 6, "SYJY27": 7}


def getDataBaseData():
    conn = pymysql.connect(host="localhost", user="root", passwd="123456", db="waterdb_ds", charset="utf8")
    cursor = conn.cursor()
    startTime = datetime.datetime.strptime("202205", "%Y%m")
    endTime = datetime.datetime.strptime("202208", "%Y%m")
    currentTime = startTime

    result = []
    while (currentTime <= endTime):
        timeStr = currentTime.strftime("%Y%m")
        sql = "select TAG_NAME,DATE_TIME,PV from service_values_day_%s where TAG_NAME in %s" % (timeStr, pointNames)
        print(sql)
        cursor.execute(sql)
        oneTable = cursor.fetchall()
        result.append(oneTable)
        currentTime += relativedelta(months=1)
        pass
    conn.commit()

    return result


def getTrainData(result, pointNum):
    dataList = pd.DataFrame(columns=["TAG_NAME", "DATE_TIME", "PV"])
    for oneMonthData in result:
        for oneData in oneMonthData:
            one = [list(oneData)]
            data = pd.DataFrame(one, columns=dataList.columns)
            dataList = dataList.append(data)
            pass
        pass

    # dataList = []
    # dataDict = {}
    # for oneMonthData in result:
    #     for oneData in oneMonthData:
    #         dataList.append(oneData)
    #         if oneData[1] in dataDict:
    #             dataDict[oneData[1]][oneData[0]] = oneData[2]
    #         else:
    #             dataDict[oneData[1]] = {}
    #             dataDict[oneData[1]][oneData[0]] = oneData[2]
    #         pass
    #     pass
    #
    # dataX = []
    # dataY = []
    # for dateTime, oneGroup in dataDict.items():
    #     data = [np.nan] * pointNum
    #     data[0] = datetime.datetime.strftime(dateTime, "%m%d%H")
    #     for pointName, val in oneGroup.items():
    #         data[pointNameDict[pointName]] = float(val)
    #         pass
    #     if np.nan not in data:
    #         dataY.append(data[7])
    #         data = data[0:7]
    #         dataX.append(data)
    #     pass
    # X = np.array(dataX)
    # Y = np.array(dataY)
    # return X, Y


# @app.post("/shunyiModelPredict")
def getPdData():
    conn = pymysql.connect(host="localhost", user="root", passwd="123456", db="waterdb_ds", charset="utf8")

    startTime = datetime.datetime.strptime("202205", "%Y%m")
    endTime = datetime.datetime.strptime("202208", "%Y%m")
    currentTime = startTime

    result = pd.DataFrame()
    while (currentTime <= endTime):
        timeStr = currentTime.strftime("%Y%m")
        sql = "select TAG_NAME,DATE_TIME,PV from service_values_day_%s where TAG_NAME in %s" % (timeStr, pointNames)
        print(sql)

        oneTableData = pd.read_sql(sql=sql, con=conn)
        result = result.append(oneTableData)
        currentTime += relativedelta(months=1)
        pass
    conn.commit()
    result = result.reset_index(drop=True)
    group = result.groupby("DATE_TIME")
    data = pd.DataFrame()
    for time, indexList in group.groups.items():
        columns = ["time"]
        vals = [time]
        for index in indexList:
            tagName = result.loc[index, "TAG_NAME"]
            columns.append(tagName)
            vals.append(result.loc[index, "PV"])
            pass
        data = data.append(pd.DataFrame([vals], columns=columns))
    return data


def neuralNet():
    result = getDataBaseData()
    # X, Y = getTrainData(result, 8)
    X, Y = getPdData()
    Xtrain, Xtest, Ytrain, Ytest = TTS(X, Y, test_size=0.3, random_state=420)
    s = []
    for i in range(100, 200, 10):
        # dnn = DNN(hidden_layer_sizes=(int(i),), activation="logistic", solver='lbfgs', random_state=420,max_iter=1000).fit(Xtrain, Ytrain)
        dnn = DNN(hidden_layer_sizes=(int(i), 10, 10), random_state=420, max_iter=1000).fit(Xtrain, Ytrain)
        s.append(dnn.score(Xtest, Ytest))
    print(len(s))
    print(max(s))
    plt.figure(figsize=(20, 5))
    plt.plot(range(100, 200, 10), s)
    plt.show()


def tree():
    rfr1 = RandomForestClassifier()

    dtc = DecisionTreeClassifier()
    model = BaggingRegressor(dtc, random_state=0)


if __name__ == '__main__':
    # create_item()
    # uvicorn.run(app=app, port=9000)
    # uvicorn.run(app=app, host="10.0.0.187", port=9050)
    getPdData()
